﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using XSTDZ.ERP.Infrastructure.AdoHelper;
using System.Web.Mvc;
using XSTDZ.ERP.Infrastructure;
using XSTDZ.ERP.WebApp.Models.Extension;
using XSTDZ.ERP.Application.DataContracts;

namespace XSTDZ.ERP.WebApp.Models.Ado
{
    /// <summary>
    /// 业务查询类 
    /// 审明：这里所有的ADO。NET 操作均为替代EF低效的操作，只是一种替代方案，不必过多在乎设计
    /// </summary>
    public class BLLQuery
    {
        #region 收付款账单

        /// <summary>
        /// 收付款账单 [返回JSONResult]
        /// </summary>
        public string GetReceivablePayableBills(HttpContextBase context)
        {
            int startIndex=0;
            int pageSize=10;
            string orderBy="";
            string whereStr = " where a.Deleted=0 ";
            int recordCount=0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string sortCode = context.Request.Form["sortCode"];
            string typeCode = context.Request.Form["typeCode"];
            string orgCode = context.Request.Form["orgCode"];

            string clientName = context.Request.Form["clientName"]??"";
            

            if (!sortCode.IsEmpty()&&sortCode.Trim()!="-1")
            {
                whereStr += string.Format(@" and a.BillClassify='{0}'", sortCode);
            }
            if (!typeCode.IsEmpty()&& typeCode != "0")
            {
                whereStr += string.Format(@" and a.BillType='{0}'", typeCode);
            }
            if (!clientName.IsEmpty() )
            {
                whereStr += string.Format(@" and (b.ClientName like '%{0}%')", clientName);
            }

            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy==""?"a.CreateTime desc":orderBy;
            whereStr += string.Format(@" and (a.OrgID in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}')) ", orgCode);

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1) from T_FI_ReceivablePayableBill a 
 inner join T_Sys_Org o on o.OrgID=a.OrgID
LEFT JOIN dbo.T_Unit_ClientInfo b on a.ClientID=b.ClientID
left join dbo.T_Sys_Refe c on (c.TypeCode in (select top 1 TypeCode from dbo.T_Sys_RefeType  where  ModuleName='" + XSTDZ.ERP.WebApp.Models.RefeTypeOption.财务账单.ToString() + "') and Convert(nvarchar(64),a.BillType)=c.RefeCode) {0}", whereStr));
           if (recordCount > 0)
           {
               var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, o.OrgName,a.ReceivablePayableBillID ID,b.ClientName ClientName,case when a.BillClassify=0 then '收款' else '付款' end BillClassifyName,
case when a.BillClassify=0 then (case when a.BillReceivable<=(a.BillReceiv+a.BillReceivPft) then '收款完成'
 when (isnull(a.BillReceiv,0)+isnull(a.BillReceivPft,0))=0 then '未开始'
 else '收款中' end)
else (case when a.BillPayable<=(a.BillPay+a.BillPayPft) then '付款完成' 
when (isnull(a.BillPay,0)+isnull(a.BillPayPft,0))=0 then '未开始'
else '付款中' end)
 end BillStateName,a.BillType,
case when a.BillClassify=0 then (case when a.BillReceivable<=(a.BillReceiv+a.BillReceivPft) then 1 else 0 end) 
else (case when a.BillPayable<=(a.BillPay+a.BillPayPft) then 1 else 0 end) end RecPayState,
c.RefeName BillTypeName,a.BillReceivable,a.BillReceiv,a.BillReceivPft,a.BillPayable,a.BillPay,a.BillPayPft,a.Remarks,
case when a.Deleted=0 then '' else '已删除' end DeleteState,case when a.IsSettle=0 then '' else '已结算' end SettleState
 from T_FI_ReceivablePayableBill a 
 inner join T_Sys_Org o on o.OrgID=a.OrgID
LEFT JOIN dbo.T_Unit_ClientInfo b on a.ClientID=b.ClientID
left join dbo.T_Sys_Refe c on (c.TypeCode in (select top 1 TypeCode from dbo.T_Sys_RefeType  where  ModuleName='" + XSTDZ.ERP.WebApp.Models.RefeTypeOption.财务账单.ToString() + "') and Convert(nvarchar(64),a.BillType)=c.RefeCode)   {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

             var target=  Newtonsoft.Json.JsonConvert.SerializeObject(result);
             return   target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
           }
           else
           {
               return "";
           }

            #endregion
        }

        public string GetReceivablePayableBillDetails(Guid Id)
        {
            string whereStr = string.Format(@" where a.Deleted=0 and a.ReceivablePayableBillID='{0}'", Id);
         
            #region Ado.Net查询
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.ReceivablePayableRecordID ID,a.ReceivedMoney,a.ReceivedMoney,a.PaidMoney,a.OmitMoney,
a.TaxMoney,a.MoreTaxMoney,a.InvoiceNO,a.ReceivedMoneyPft
,b.RefeName InvoiceTypeName,
c.RefeName PayTypeName,a.Remarks
 from T_FI_ReceivablePayableRecord a
 left join dbo.T_Sys_Refe b on a.InvoiceType=b.RefeID
 left join dbo.T_Sys_Refe c on a.PayType=c.RefeID {0}  order by a.CreateTime desc", whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";
           
            #endregion
        }
        #endregion
        #region 损溢报告
        /// <summary>
        ///  损溢报告 [返回JSONResult]
        /// </summary>
        public string GetProfitAndLossBills(HttpContextBase context)
        {
            int startIndex=0;
            int pageSize=10;
            string orderBy="";
            string whereStr = " where 1=1 and a.Deleted=0";
            int recordCount=0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string billNo = context.Request.Form["billNo"];
            string auditName = context.Request.Form["auditName"];
            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            whereStr += string.Format(@" and a.StorehouseID in (
	select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	on org.OrgID=st.OrgID
	where org.Code='{0}'
) ", orgCode);

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and b.ProfitAndLossNo like '%{0}%'", billNo);
            }
            if (!auditName.IsEmpty())
            {
                whereStr += string.Format(@" and u.Name like '%{0}%'", auditName);
            }
            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.CreateTime>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {

                whereStr += string.Format(@" and a.CreateTime<='{0}'", Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy==""?"a.CreateTime desc":orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  from T_PD_ProfitAndLossBill a
left join dbo.T_PD_Storehouse b 
on a.StorehouseID=b.StorehouseID
left join  dbo.T_Sys_User u on u.UserID=a.CheckorID {0}", whereStr));
           if (recordCount > 0)
           {
               var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.ProfitAndLossBillID Id,a.ProfitAndLossNo,a.CheckTime,b.StorehouseName,
a.Remarks,
u.Name CheckorName,
a.CheckState,a.BillState,
case when a.BillState=0 then '保存' else '提交' end BillStateName,
case when a.CheckState=0 then '' when   a.CheckState=1 then '审核通过' else '审核未通过' end CheckStateName,
case when a.IsSettle=0 then '' else '结算' end SettleState,
case when a.Deleted=0 then '' else '已删除' end DeleteState
from T_PD_ProfitAndLossBill a
left join dbo.T_PD_Storehouse b 
on a.StorehouseID=b.StorehouseID
left join  dbo.T_Sys_User u on u.UserID=a.CheckorID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

             var target=  Newtonsoft.Json.JsonConvert.SerializeObject(result);
             return   target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
           }
           else
           {
               return "";
           }

            #endregion
        }
        /// <summary>
        /// 获得明细(损溢报告)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetProfitAndLossDetails(Guid billid)
        {

            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ProfitAndLossBillDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from dbo.T_PD_ProfitAndLossBillDetail  a 
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ProfitAndLossBillID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 销售数据(产品销售)
        /// <summary>
        /// 销售数据 [返回JSONResult]
        /// </summary>
        public string GetProductSellBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where a.Deleted=0 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string clientName = context.Request.Form["clientName"];
            string fhName = context.Request.Form["fhName"];
            string auditSate = context.Request.Form["auditSate"];
            string billNo = context.Request.Form["billNo"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];


            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.ProductSellBillo like '%{0}%'", billNo);
            }
            if (!clientName.IsEmpty())
            {
                whereStr += string.Format(@" and b.ClientName like '%{0}%'", clientName);
            }
            if (!fhName.IsEmpty())
            {
                whereStr += string.Format(@" and e.Name like '%{0}%'", fhName);
            }
            if (!auditSate.IsEmpty())
            {
                whereStr += string.Format(@" and a.AuditState='{0}'", auditSate);
            }

            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.SellTime>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.SellTime<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }
         

            startIndex = startIndex > 0 ? startIndex - 1 : 0;


            whereStr += string.Format(@" and a.StorehouseID in (
	select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	on org.OrgID=st.OrgID
	where org.Code='{0}'
) ", orgCode);

            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1) from  T_PD_ProductSellBill a 
 left join dbo.T_Unit_ClientInfo b on a.ClientID=b.ClientID
 left join dbo.T_Sys_User c on c.UserID=a.SenderId
 left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
 left join dbo.T_Sys_User e on e.UserID=a.SellorID
left join dbo.T_Sys_User f on f.UserID=a.CreateID
left join dbo.T_Sys_User g on g.UserID=a.AuditorID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ProductSellBillID ID,a.ProductSellBillo ProductSellBillNo,a.ClientOrderNo,a.SellTime
,
(select round(SUM(SellAmount*SellPrice),2) from dbo.T_PD_ProductSellBillDetail where Deleted=0 and  ProductSellBillID =a.ProductSellBillID)
as SellMoney
,case when a.IsSettle=0 then '' else '结算' end SettleState,
a.BillState,
b.ClientName ClientName,c.Name SenderName,case when a.Deleted=0 then '' else '已删除' end DeleteState,
d.StorehouseName StorehouseName,e.Name SellerName,f.Name CreateName,
case when a.AuditState=0 then '' when a.AuditState=1 then '审核通过' else '审核未通过' end AuditStateName,
a.AuditTime,a.AuditState,
  case when a.BillState=0 then '保存' else '提交' end BillStateName,
g.Name AuditorName
 from T_PD_ProductSellBill a 
 left join dbo.T_Unit_ClientInfo b on a.ClientID=b.ClientID
 left join dbo.T_Sys_User c on c.UserID=a.SenderId
 left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
 left join dbo.T_Sys_User e on e.UserID=a.SellorID
left join dbo.T_Sys_User f on f.UserID=a.CreateID
left join dbo.T_Sys_User g on g.UserID=a.AuditorID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }

        /// <summary>
        /// 获得明细(销售数据明细)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetProductSellDetails(Guid billid)
        {


            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ProductSellBillDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_PD_ProductSellBillDetail a 
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ProductSellBillID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 发货(产品销售)
        /// <summary>
        /// 发货 [返回JSONResult]
        /// </summary>
        public string GetProductDeliveryBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 and a.Deleted!=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string isConfirm = context.Request.Form["isConfirm"];
            string selectType = context.Request.Form["selectType"];

            string billNo = context.Request.Form["billNo"];
         
            string fhName = context.Request.Form["fhName"];
            string auditSate = context.Request.Form["auditSate"];


            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            if (!selectType.IsEmpty() && selectType == "confirm")
            {
                whereStr += @" and a.BillState=1 ";//只查询提交后，未确定的单子
                if (!isConfirm.IsEmpty() && isConfirm != "-1")
                {
                    whereStr += string.Format(" and a.AffirmState={0} ", isConfirm);
                }
              //  whereStr += @" and a.BillState=1 and a.AffirmState=0 ";//只查询提交后，未确定的单子

                whereStr += string.Format(@" and a.DeliveryID in (
	                select OrgID from dbo.T_Sys_Org org where org.Code='{0}'
                ) ", orgCode);
            }
            else
            {
                whereStr += string.Format(@" and a.StorehouseID in (
	                select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	                on org.OrgID=st.OrgID
	                where org.Code='{0}'
                ) ", orgCode);
            }

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.DeliveryNo like '%{0}%'", billNo);
            }

            if (!fhName.IsEmpty())
            {
                whereStr += string.Format(@" and e.Name like '%{0}%'", fhName);
            }
            if (!auditSate.IsEmpty())
            {
                whereStr += string.Format(@" and a.BillState='{0}'", auditSate);
            }

            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.DeliveryDate>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.DeliveryDate<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }
         


            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from T_PD_ProductDeliveryBill a
  left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
   left join dbo.T_Sys_User e on e.UserID=a.DeliveryorID
   left join dbo.T_Sys_User f on f.UserID=a.SenderID
   left join dbo.T_Sys_Dept o on o.DeptID=a.DeliveryID
    left join dbo.T_Sys_User h on h.UserID=a.AffirmorID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ProductDeliveryBillID Id,a.DeliveryNo,a.DeliveryDate
,d.StorehouseName StorehouseName,e.Name DeliveryorName,f.Name SenderName,
o.OrgName DeliveryName,h.Name AffirmorName,a.AffirmDate,a.AffirmState,
case when a.AffirmState=0 then '' else '已确认' end AffirmStateName,
case when a.IsSettle=0 then '' else '结算' end SettleState
,case when a.Deleted=0 then '' else '已删除' end DeleteState,
case when a.BillState=0 then '保存' else '提交' end BillStateName,
a.BillState,
a.Remarks
 from T_PD_ProductDeliveryBill a
  left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
   left join dbo.T_Sys_User e on e.UserID=a.DeliveryorID
   left join dbo.T_Sys_User f on f.UserID=a.SenderID
   left join dbo.T_Sys_Org o on o.OrgID=a.DeliveryID
    left join dbo.T_Sys_User h on h.UserID=a.AffirmorID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
       /// <summary>
       /// 获得明细(发货)
       /// </summary>
       /// <param name="billid"></param>
       /// <returns></returns>
        public string GetProductDeliveryDetails(Guid billid)
        {
           
          
            #region Ado.Net查询




            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ProductDeliveryBillDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_PD_ProductDeliveryBillDetail a 
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where ProductDeliveryBillID='{0}' and a.Deleted!=1  ", billid));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";
           

            #endregion
        } 
        #endregion
        #region 入库订单(入库订单)
        /// <summary>
        /// 入库订单 [返回JSONResult]
        /// </summary>
        public string GetProductInBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string billNo = context.Request.Form["billNo"];
            string fhName = context.Request.Form["fhName"];
            string auditSate = context.Request.Form["auditSate"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.ProductInBillNo like '%{0}%'", billNo);
            }

            if (!fhName.IsEmpty())
            {
                whereStr += string.Format(@" and h.Name like '%{0}%'", fhName);
            }
            if (!auditSate.IsEmpty())
            {
                whereStr += string.Format(@" and a.IsSettle='{0}'", auditSate);
            }

            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.ProductInDate>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.ProductInDate<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }

            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            whereStr += string.Format(@" and a.StorehouseID in (
	                select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	                on org.OrgID=st.OrgID
	                where org.Code='{0}'
                ) ", orgCode);
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from T_PD_ProductInBill a
     left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
     left join dbo.T_Sys_User h on h.UserID=a.EmployeeID
     left join dbo.T_Sys_Org o on o.OrgID=a.SourceOrgID
      left join dbo.T_Sys_User f on f.UserID=a.SenderID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.ProductInBillID ID,a.ProductInBillNo,a.ProductInDate
    ,d.StorehouseName StorehouseName,h.Name EmployeeName,
    o.OrgName SourceOrgName,f.Name SenderName,a.Remarks,
a.BillState,
    case when a.IsSettle=0 then '' else '结算' end SettleState
   ,case when a.BillState=0 then '保存' else '提交' end BillStateName
     from T_PD_ProductInBill a
     left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
     left join dbo.T_Sys_User h on h.UserID=a.EmployeeID
     left join dbo.T_Sys_Org o on o.OrgID=a.SourceOrgID
      left join dbo.T_Sys_User f on f.UserID=a.SenderID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        /// 获得明细(入库)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetProductInDetails(Guid billid)
        {


            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,c.ProductName,a.ProductInBillDetailID ID,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_PD_ProductInBillDetail a 
inner join dbo.T_Base_ProductInfo c on a.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ProductInBillID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 产品盘点
        /// <summary>
        ///  产品盘点 [返回JSONResult]
        /// </summary>
        public string GetProductCheckBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 and a.Deleted=0 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string billNo = context.Request.Form["billNo"];
            string pdName = context.Request.Form["pdName"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.CheckNo like '%{0}%'", billNo);
            }
            if (!pdName.IsEmpty())
            {
                whereStr += string.Format(@" and h.Name like '%{0}%'", pdName);
            }
            if (!StartDate.IsEmpty())
            {
                whereStr += string.Format(@" and a.StartTime>='{0}'", StartDate);
            }
            if (!EndDate.IsEmpty())
            {
                whereStr += string.Format(@" and a.EndTime<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }


            startIndex = startIndex > 0 ? startIndex - 1 : 0;

            whereStr += string.Format(@" and a.StorehouseID in (
	select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	on org.OrgID=st.OrgID
	where org.Code='{0}'
) ", orgCode);

            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  from T_PD_ProductCheckBill a
       left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
        left join dbo.T_Sys_User h on h.UserID=a.CheckorID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.ProductCheckBillID Id,a.CheckNo,
      a.StartTime,a.EndTime,a.Remarks
      ,d.StorehouseName StorehouseName,a.BillState,
      h.Name CheckorName,
       case when a.IsSettle=0 then '' else '结算' end SettleState
    ,case when a.Deleted=0 then '' else '已删除' end DeleteState
   ,case when a.BillState=0 then '保存' else '提交' end BillStateName
       from T_PD_ProductCheckBill a
       left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
        left join dbo.T_Sys_User h on h.UserID=a.CheckorID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        /// 获得明细(产品盘点)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetProductCheckDetails(Guid billid)
        {
            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ProductCheckBillDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_PD_ProductCheckBillDetail a 
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ProductCheckBillID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 销售退货
        /// <summary>
        ///  销售退货 [返回JSONResult]
        /// </summary>
        public string GetProductReturnBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string billNo = context.Request.Form["billNo"];
            string returnClient = context.Request.Form["returnClient"];
            string auditSate = context.Request.Form["auditSate"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];


            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.ReturnBillNo like '%{0}%'", billNo);
            }
            if (!returnClient.IsEmpty())
            {
                whereStr += string.Format(@" and c.ClientName like '%{0}%'", returnClient);
            }

            if (!auditSate.IsEmpty())
            {
                whereStr += string.Format(@" and a.AuditState ='{0}'", auditSate);
            }
            if (StartDate.IsData())
             {
                 whereStr += string.Format(@" and a.ReturnTime>='{0}'", StartDate);
             }
            if (EndDate.IsData())
             {
                 whereStr += string.Format(@" and a.ReturnTime<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
             }


            startIndex = startIndex > 0 ? startIndex - 1 : 0;

            whereStr += string.Format(@" and a.StorehouseID in (
	select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	on org.OrgID=st.OrgID
	where org.Code='{0}'
) ", orgCode);
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from dbo.T_PD_ProductReturnBill a
        left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
        left join dbo.T_Unit_ClientInfo c on c.ClientID=a.ClientID
        left join dbo.T_Sys_User h on h.UserID=a.AuditorID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ProductReturnBillID Id,a.ReturnBillNo
      ,d.StorehouseName,c.ClientName ClientName,(select round(SUM(ReturnAmount*ReturnPrice),2) from dbo.T_PD_ProductReturnBillDetail where Deleted=0 and  ProductReturnBillID=a.ProductReturnBillID)
       as ReturnMoney,a.ReturnTime
      ,h.Name AuditorName,a.AuditTime,a.BillState,
      case when a.AuditState=0 then '' else '已审核' end AffirmStateName,
      case when a.IsSettle=0 then '' else '结算' end SettleState
,case when a.Deleted=0 then '' else '已删除' end DeleteState,
case when a.BillState=1 then '提交' else '保存' end BillStateName
,a.AuditState
       from dbo.T_PD_ProductReturnBill a
        left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
        left join dbo.T_Unit_ClientInfo c on c.ClientID=a.ClientID
        left join dbo.T_Sys_User h on h.UserID=a.AuditorID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        /// 获得明细(销售退货)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetProductReturnDetails(Guid billid)
        {


            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ProductReturnBillDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_PD_ProductReturnBillDetail a 
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ProductReturnBillID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 工作组列表
        /// <summary>
        ///  工作组列表 [返回JSONResult]
        /// </summary>
        public string GetWorkGoroups(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = string.Format(" where u.IsPiece='1' and a.Deleted=0 and c.ModuleName='{0}' ", RefeTypeOption.工作组名称.ToString());
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            //string billNo = context.Request.Form["billNo"];
            //string returnClient = context.Request.Form["returnClient"];
            //string returnDate = context.Request.Form["returnDate"];
            //string auditSate = context.Request.Form["auditSate"];

            //if (!string.IsNullOrEmpty(billNo))
            //{
            //    whereStr += string.Format(@" and a.ReturnBillNo like '%{0}%'", billNo);
            //}
            //if (!string.IsNullOrEmpty(returnClient))
            //{
            //    whereStr += string.Format(@" and c.ClientName like '%{0}%'", returnClient);
            //}
            //if (!string.IsNullOrEmpty(returnDate))
            //{
            //    whereStr += string.Format(@" and a.ReturnTime>= '{0}'", returnDate);
            //}
            //if (!string.IsNullOrEmpty(auditSate))
            //{
            //    whereStr += string.Format(@" and a.AuditState ='{0}'", auditSate);
            //}
            startIndex = startIndex > 0 ? startIndex - 1 : 0;

//            whereStr += string.Format(@" and a.StorehouseID in (
//	select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
//	on org.OrgID=st.OrgID
//	where org.Code='{0}'
//) ", orgCode);
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.GroupName  asc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  FROM [XSC_XST_ERP].[dbo].[T_FI_WorkGroup] a
  inner join dbo.T_Sys_Refe b on a.GroupName=b.RefeCode
  inner join T_Sys_RefeType c on c.TypeCode=b.TypeCode
  inner join dbo.T_Sys_User u on u.UserID=a.UserID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.[WorkGroupID] Id
      ,a.[GroupName]
      ,a.[UserID]
      ,a.[IsHeadman]
      ,case when [IsHeadman]=0 then '组员' else '组长' end IsHeadmanName
      ,a.[Remarks]
      ,b.RefeName
      ,u.Name
  FROM [XSC_XST_ERP].[dbo].[T_FI_WorkGroup] a
  inner join dbo.T_Sys_Refe b on a.GroupName=b.RefeCode
  inner join T_Sys_RefeType c on c.TypeCode=b.TypeCode
  inner join dbo.T_Sys_User u on u.UserID=a.UserID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 退货单
        /// <summary>
        ///  退货单 [返回JSONResult]
        /// </summary>
        public string GetReturnBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];
           
            string isConfirm = context.Request.Form["isConfirm"];

            string isChengDu = context.Request.Form["HidChengDu"];//确认时用
            string selectType = context.Request.Form["selectType"];//若是确认，而以收货单位来判断，否则选择仓库


            string billNo = context.Request.Form["billNo"];
            string returnClient = context.Request.Form["returnClient"];
            string auditSate = context.Request.Form["auditSate"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];


            if (!selectType.IsEmpty()&& selectType == "confirm")
            {
                whereStr += @" and a.BillState=1 ";//只查询提交后，未确定的单子
                if (!isConfirm.IsEmpty() && isConfirm!="-1")
                {
                    whereStr += string.Format(" and a.AffirmState={0} ",isConfirm);
                }

                whereStr += string.Format(@" and a.DeliveryID in (select OrgID from dbo.T_Sys_Org org where org.Code='{0}') ", orgCode);
            }
            else
            {
                whereStr += string.Format(@" and a.StorehouseID in (
	                select st.StorehouseID from dbo.T_Sys_Org org left join T_PD_Storehouse st
	                on org.OrgID=st.OrgID
	                where org.Code='{0}'
                ) ", orgCode);
            }

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.ReturnBillNo like '%{0}%'", billNo);
            }
            if (!returnClient.IsEmpty())
            {
                whereStr += string.Format(@" and h.Name like '%{0}%'", returnClient);
            }

            if (!auditSate.IsEmpty())
            {
                whereStr += string.Format(@" and a.BillState ='{0}'", auditSate);
            }
            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.ReturnTime>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.ReturnTime<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }


            startIndex = startIndex > 0 ? startIndex - 1 : 0;

           

            whereStr += " and a.Deleted!=1 ";
            #endregion


            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from T_FT_ReturnBill a
        left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
        left join dbo.T_Sys_User h on h.UserID=a.ReturnBillorID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ReturnBillID Id,a.ReturnBillNo ,d.StorehouseName,
        h.Name ReturnBillorName,
        a.AffirmDate,a.Remarks,a.ReturnTime,
        o.orgName,a.AffirmState,
        (select top 1 Name  from T_Sys_User f where f.UserID=SenderID) SenderName,
        (select top 1 Name  from T_Sys_User g where g.UserID=AffirmorID) AffirmorName,
        case when a.AffirmState=0 then '' else '已审核' end AffirmStateName,
        case when a.IsSettle=0 then '' else '结算' end SettleState,
        case when a.Deleted=0 then '' else '已删除' end DeleteState,
        case when a.BillState=1 then '提交' else '保存' end BillStateName,
a.BillState
        from T_FT_ReturnBill a
        left join dbo.T_PD_Storehouse d on d.StorehouseID=a.StorehouseID
        left join dbo.T_Sys_User h on h.UserID=a.ReturnBillorID
        left join dbo.T_Sys_Org o on o.OrgID=a.DeliveryID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        /// 获得明细(退货单)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetReturnDetails(Guid billid)
        {


            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ReturnBillDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_FT_ReturnBillDetail a 
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ReturnBillID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 用户信息
        /// <summary>
        ///  用户信息 [返回JSONResult]
        /// </summary>
        public string GetUserLists(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = "";
            if ((context.Request["selectype"] ?? "").ToString() == "ispiece")
            {
                whereStr = string.Format(" where (accountName<>'admin' and name<>'admin') and a.Deleted!=1 and a.IsPiece='1' and a.UserId!='{0}' ", XSTDZ.ERP.Infrastructure.SystemConfig.AdminId);
            }
            else
            {
                whereStr = string.Format(" where (accountName<>'admin' and name<>'admin') and a.Deleted!=1 and a.UserId!='{0}' ", XSTDZ.ERP.Infrastructure.SystemConfig.AdminId);

            }
            int recordCount = 0;
            #region 变量赋值
            var p = context.Request["page"] ?? "0" ;
            var size = context.Request["pageSize"] ?? "999";
            Int32.TryParse(p.ToString(), out startIndex);
            Int32.TryParse(size.ToString(), out pageSize);

            string orgCode = context.Request["orgCode"];

            string userName = context.Request["userName"];
            string orgName = context.Request["orgName"];
            string deptName = context.Request["deptName"];
            string isEnabled = context.Request["isEnabled"];

            if (!orgCode.IsEmpty())
            {
                if (orgCode.Trim().ToUpper() == SystemConfig.XinChangCode.ToUpper() || orgCode.Trim().ToUpper() == SystemConfig.LaoChangCode.ToUpper())
                {
                    whereStr += string.Format(@" and a.OrgID in (select OrgID from dbo.T_Sys_Org org  where (org.Code='{0}' or org.Code='{1}')) ", SystemConfig.XinChangCode, SystemConfig.LaoChangCode);
                }
                else
                {
                    whereStr += string.Format(@" and a.OrgID in (select OrgID from dbo.T_Sys_Org org  where org.Code='{0}') ", orgCode);

                }
            }

            if (!userName.IsEmpty())
            {
                whereStr += string.Format(@" and (a.AccountName like '%{0}%' or a.Name like '%{0}%')", userName);
            }
            if (!orgName.IsEmpty())
            {
                whereStr += string.Format(@" and h.OrgName like '%{0}%'", orgName);
            }
            if (!deptName.IsEmpty())
            {
                whereStr += string.Format(@" and d.DeptName like '%{0}%'", deptName);
            }
            if (!isEnabled.IsEmpty())
            {
                whereStr += string.Format(@" and isnull(a.IsUse,0) ='{0}'", isEnabled);
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from dbo.T_Sys_User a
   left join dbo.T_Sys_Org h on h.OrgID=a.OrgID
   left join dbo.T_Sys_Dept d on d.DeptID=a.DeptID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.UserID [Key],a.AccountName LoginName,a.Name,
  case when a.Gender=0 then '女' else '男' end Gender,
  a.Birthday,a.Mobile,a.Tel,a.QQ,
  h.OrgName OrgName, d.DeptName DeptName,
(select top 1 roleId from dbo.T_Sys_UserRole ur where ur.UserId=a.UserID) RoleId,
  case when a.IsUse=1 then '正常' else '禁用' end IsEnable
   from dbo.T_Sys_User a
   left join dbo.T_Sys_Org h on h.OrgID=a.OrgID
   left join dbo.T_Sys_Dept d on d.DeptID=a.DeptID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        public string GetAllUsers(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = "";
           
           whereStr = string.Format(" where (accountName<>'admin' and name<>'admin') and a.Deleted!=1 and a.IsPiece='0' and a.UserId!='{0}' ", XSTDZ.ERP.Infrastructure.SystemConfig.AdminId);
            

            int recordCount = 0;
            #region 变量赋值
            var p = context.Request["page"] ?? "0";
            var size = context.Request["pageSize"] ?? "999";
            Int32.TryParse(p.ToString(), out startIndex);
            Int32.TryParse(size.ToString(), out pageSize);

            string orgCode = context.Request["orgCode"];

            string userName = context.Request["userName"];
            string orgName = context.Request["orgName"];
            string deptName = context.Request["deptName"];
            string isEnabled = context.Request["isEnabled"];

            //if (!orgCode.IsEmpty())
            //{
            //    if (orgCode.Trim().ToUpper() == SystemConfig.XinChangCode.ToUpper() || orgCode.Trim().ToUpper() == SystemConfig.LaoChangCode.ToUpper())
            //    {
            //        whereStr += string.Format(@" and a.OrgID in (select OrgID from dbo.T_Sys_Org org  where (org.Code='{0}' or org.Code='{1}')) ", SystemConfig.XinChangCode, SystemConfig.LaoChangCode);
            //    }
            //    else
            //    {
            //        whereStr += string.Format(@" and a.OrgID in (select OrgID from dbo.T_Sys_Org org  where org.Code='{0}') ", orgCode);

            //    }
            //}

            if (!userName.IsEmpty())
            {
                whereStr += string.Format(@" and (a.AccountName like '%{0}%' or a.Name like '%{0}%')", userName);
            }
            if (!orgName.IsEmpty())
            {
                whereStr += string.Format(@" and h.OrgName like '%{0}%'", orgName);
            }
            if (!deptName.IsEmpty())
            {
                whereStr += string.Format(@" and d.DeptName like '%{0}%'", deptName);
            }
            if (!isEnabled.IsEmpty())
            {
                whereStr += string.Format(@" and isnull(a.IsUse,0) ='{0}'", isEnabled);
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "h.OrgName , a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from dbo.T_Sys_User a
   left join dbo.T_Sys_Org h on h.OrgID=a.OrgID
   left join dbo.T_Sys_Dept d on d.DeptID=a.DeptID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.UserID [Key],a.AccountName LoginName,a.Name,
  case when a.Gender=0 then '女' else '男' end Gender,
  a.Birthday,a.Mobile,a.Tel,a.QQ,
  h.OrgName OrgName, d.DeptName DeptName,
(select top 1 roleId from dbo.T_Sys_UserRole ur where ur.UserId=a.UserID) RoleId,
  case when a.IsUse=1 then '正常' else '禁用' end IsEnable
   from dbo.T_Sys_User a
   left join dbo.T_Sys_Org h on h.OrgID=a.OrgID
   left join dbo.T_Sys_Dept d on d.DeptID=a.DeptID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 产品信息(有无库存都存在)
        /// <summary>
        ///  产品信息 [返回JSONResult]
        /// </summary>
        public string GetProductLists(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = @"  where r.typecode in (
      select t.typecode from dbo.T_Sys_RefeType t where t.modulename='计量单位'
      )  ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string ProductName = context.Request.Form["ProductName"];
            string ProductCode = context.Request.Form["ProductCode"];
            string TypeName = context.Request.Form["TypeName"];
            string ProductGG = context.Request.Form["ProductGG"];

            if (!ProductName.IsEmpty())
            {
                whereStr += string.Format(@" and (a.ProductName like '%{0}%' or a.ProductPyName like '%{0}%') ", ProductName);
            }
            if (!ProductCode.IsEmpty())
            {
                whereStr += string.Format(@" and a.ProductCode like '%{0}%'", ProductCode);
            }

            if (!ProductGG.IsEmpty())
            {
                whereStr += string.Format(@" and a.ProductStandard like '%{0}%'", ProductGG);
            }
            if (!TypeName.IsEmpty())
            {
                whereStr += string.Format(@" and h.TypeName like '%{0}%'", TypeName);
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.ProductName " : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  from T_Base_ProductInfo a
      inner join dbo.T_Base_ProductType h on h.TypeID=a.ProductTypeID
      left join dbo.T_Sys_Refe r
      on convert(varchar,r.refecode)=convert(varchar,a.ProductUint) {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ProductID ID,a.ProductName,a.ProductCode,
   a.ProductStandard,a.MnemonicCode,a.ProductUint,a.SellPrice,
   a.Remarks,h.TypeName TypeName
   ,case when a.Deleted=0 then '' else '已删除' end DeleteState,
   r.refename
    from T_Base_ProductInfo a
      left join dbo.T_Base_ProductType h on h.TypeID=a.ProductTypeID
      left join dbo.T_Sys_Refe r
      on convert(varchar,r.refecode)=convert(varchar,a.ProductUint)
       {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 产品信息(有库存)
        /// <summary>
        ///  产品信息 [返回JSONResult]
        /// </summary>
        public string GetProductStocks(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where b.Deleted=0 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];
            string TypeName = context.Request.Form["TypeName"];
            string ProductName = context.Request.Form["ProductName"];
            string ProductCode = context.Request.Form["ProductCode"];
            string ProductGG = context.Request.Form["ProductGG"];

            if (!TypeName.IsEmpty())
            {
                whereStr += string.Format(@" and h.TypeName like '%{0}%'", TypeName);
            }
            if (!ProductName.IsEmpty())
            {
                whereStr += string.Format(@" and (b.ProductName like '%{0}%' or b.ProductPyName like '%{0}%') ", ProductName);
            }
            if (!ProductCode.IsEmpty())
            {
                whereStr += string.Format(@" and b.ProductCode like '%{0}%'", ProductCode);
            }

            if (!ProductGG.IsEmpty())
            {
                whereStr += string.Format(@" and b.ProductStandard like '%{0}%'", ProductGG);
            }
            whereStr += string.Format(@" and c.OrgID in (select OrgID from dbo.T_Sys_Org org  where org.Code='{0}') ", orgCode);
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? " b.ProductName " : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1) from dbo.T_PD_Stock a
      inner join T_Base_ProductInfo b on a.ProductID=b.ProductID
      inner join dbo.T_Base_ProductType h on h.TypeID=b.ProductTypeID
      inner join dbo.T_PD_Storehouse c on c.StorehouseID=a.StorehouseID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.StockID,a.StorehouseID,a.ProductID,a.StockCount,b.ProductName,b.ProductCode,b.ProductStandard,
      c.StorehouseName,b.SellPrice,h.TypeName TypeName 
      from dbo.T_PD_Stock a
      inner join T_Base_ProductInfo b on a.ProductID=b.ProductID
      inner join dbo.T_PD_Storehouse c on c.StorehouseID=a.StorehouseID
    inner join dbo.T_Base_ProductType h on h.TypeID=b.ProductTypeID
  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 产品工序
        /// <summary>
        ///  产品工序 [返回JSONResult]
        /// </summary>
        public string GetProductProducts(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);

            string ProductName = context.Request.Form["ProductName"];
            string ProductCode = context.Request.Form["ProductCode"];
            string ProductGG = context.Request.Form["ProductGG"];
            string ProcedureGXName = context.Request.Form["ProcedureGXName"];

            if (!ProductName.IsEmpty())
            {
                whereStr += string.Format(@" and (p.ProcedureName like '%{0}%' or p.ProcedureNo like '%{0}%') ", ProductName);
            }
            if (!ProductCode.IsEmpty())
            {
                whereStr += string.Format(@" and p.ProductCode like '%{0}%'", ProductCode);
            }

            if (!ProductGG.IsEmpty())
            {
                whereStr += string.Format(@" and p.ProductStandard like '%{0}%'", ProductGG);
            }
            if (!ProcedureGXName.IsEmpty())
            {
                whereStr += string.Format(@" and (a.ProcedureName like '%{0}%' or  a.ProcedureNo)", ProcedureGXName);
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.ProductProcedureID " : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  from T_FI_ProductProcedure a 
inner join dbo.T_Base_ProductInfo p on a.ProductID=p.ProductID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ProductProcedureID ID,p.ProductName ProductName,a.ProcedureNo,a.ProcedureName
,a.HourMoney,a.Remarks
  from T_FI_ProductProcedure a 
inner join dbo.T_Base_ProductInfo p on a.ProductID=p.ProductID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 客户信息(经销商)联系人
        /// <summary>
        ///  客户信息(经销商)联系人 [返回JSONResult]
        /// </summary>
        public string GetClientLinksLists(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string ClientName = context.Request.Form["ClientName"];
            string LinkName = context.Request.Form["LinkName"];


            if (!ClientName.IsEmpty())
            {
                whereStr += string.Format(@" and c.ClientName like '%{0}%' ", ClientName);
            }
            if (!LinkName.IsEmpty())
            {
                whereStr += string.Format(@" and a.LinkManNmae like '%{0}%'", LinkName);
            }
                whereStr += @" and isnull(a.Deleted,0) ='0'";
       
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            whereStr += string.Format(@" and c.OrgID  in (
	select OrgID from dbo.T_Sys_Org org where org.Code='{0}'
) ", orgCode);
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.CreateTime" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  from T_Unit_ClientLink a
 left join dbo.T_Sys_Refe r on (a.CredentialsType=R.RefeCode AND R.TypeCode='42CBDF1F-1C1B-491F-9DB6-BD8EE23E0701')
 inner join dbo.T_Unit_ClientInfo c on a.ClientID=c.ClientID  {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.LinkManID Id,a.ClientID,a.LinkManNmae,
case when a.Sex=1 then '男' else '女' end SexName,
R.RefeName CredentialsTypeName,a.CredentialsNo,a.Department,
a.CurrentJob,a.BusinessScope,a.MobilePhone,a.StudioCamera,
c.ClientName
 from T_Unit_ClientLink a
 left join dbo.T_Sys_Refe r on (a.CredentialsType=R.RefeCode AND R.TypeCode='42CBDF1F-1C1B-491F-9DB6-BD8EE23E0701')
 inner join dbo.T_Unit_ClientInfo c on a.ClientID=c.ClientID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        
        #endregion
        #region 客户信息(经销商)
        /// <summary>
        ///  客户信息(经销商) [返回JSONResult]
        /// </summary>
        public string GetClientLists(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where a.Deleted=0 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request["orgCode"];// context.Request.Form["orgCode"];
            string haseal = context.Request["haseal"]??"0";//是否有销售过
            string ClientName = context.Request.Form["ClientName"];
            string ClientCode = context.Request.Form["ClientCode"];
            string Deleted = context.Request.Form["Deleted"];

            if (!haseal.IsEmpty() && haseal=="1")
            {
                whereStr += @" and exists(
select top 1 1 from dbo.T_PD_ProductSellBill t where t.ClientID=a.ClientID) ";
            }

            if (!orgCode.IsEmpty())
            {
                whereStr += string.Format(@" and a.OrgID  in (
	select OrgID from dbo.T_Sys_Org org where org.Code='{0}'
) ", orgCode);
            }

            if (!ClientName.IsEmpty())
            {
                whereStr += string.Format(@" and a.ClientName like '%{0}%' ", ClientName);
            }
            if (!ClientCode.IsEmpty())
            {
                whereStr += string.Format(@" and a.ClientCode like '%{0}%'", ClientCode);
            }

            if (!Deleted.IsEmpty())
            {
                whereStr += string.Format(@" and isnull(a.Deleted,0) ='{0}'", Deleted);
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
           
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1) from T_Unit_ClientInfo a {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.ClientID [Key],a.ClientName,a.ClientCode,a.ShortName,a.ClientPhone,
a.Email,a.BankName,a.AccountNnmber,a.ClientAddress
,case when a.Deleted=0 then '' else '已删除' end DeleteState,b.[Name]
 from T_Unit_ClientInfo a left join dbo.T_Sys_User b on a.ResponsiblePersonID=b.UserID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
      
        #endregion
        #region 产品调拔
        /// <summary>
        ///  产品调拔 [返回JSONResult]
        /// </summary>
        public string GetProductAllotBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);

            string billNo = context.Request.Form["billNo"];
            string FromClientName = context.Request.Form["FromClientName"];
            string ToClientName = context.Request.Form["ToClientName"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and a.AllotNo like '%{0}%'", billNo);
            }
            if (!FromClientName.IsEmpty())
            {
                whereStr += string.Format(@" and f.ClientName like '%{0}%'", FromClientName);
            }
            if (!ToClientName.IsEmpty())
            {
                whereStr += string.Format(@" and t.ClientName like '%{0}%'", ToClientName);
            }

            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.CreateTime>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.CreateTime<='{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)    from T_PD_ProductAllot a
 left join T_Sys_Org o on o.OrgID=a.AllotOrgID
 left join dbo.T_Sys_User u on u.UserID=a.AllotorID
  left join T_Unit_ClientInfo f on f.ClientID=a.FromClientID
  left join dbo.T_Unit_ClientInfo t on t.ClientID=a.ToClientID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.ProductAllotID Id,a.AllotNo,a.Remarks,
o.OrgName AllotOrgName,u.Name AllotorName,a.CreateTime,
f.ClientName FromClientName,t.ClientName ToClientName,
case when a.IsSettle=0 then '' else '结算' end SettleState
    ,case when a.Deleted=0 then '' else '已删除' end DeleteState
,case when a.BillState=0 then '保存' else '提交' end BillStateName
    ,a.BillState
 from T_PD_ProductAllot a
 left join T_Sys_Org o on o.OrgID=a.AllotOrgID
 left join dbo.T_Sys_User u on u.UserID=a.AllotorID
  left join T_Unit_ClientInfo f on f.ClientID=a.FromClientID
  left join dbo.T_Unit_ClientInfo t on t.ClientID=a.ToClientID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        /// 获得明细(产品调拔)
        /// </summary>
        /// <param name="billid"></param>
        /// <returns></returns>
        public string GetProductAllotDetails(Guid billid)
        {


            #region Ado.Net查询

            var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select a.*,a.ProductAllotDetailID ID,c.ProductName,c.ProductCode,c.ProductStandard,d.TypeName
 from  dbo.T_PD_ProductAllotDetail a 
inner join dbo.T_PD_ProductSellBillDetail s on a.ProductSellBillDetailID=s.ProductSellBillDetailID
inner join dbo.T_PD_Stock b on s.StockID=b.StockID
inner join dbo.T_Base_ProductInfo c on b.ProductID=c.ProductID
inner join dbo.T_Base_ProductType d on d.TypeID=c.ProductTypeID
where a.ProductAllotID='{0}' and a.Deleted!=1  ", billid));

            var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
            return target = "{\"Rows\":" + target + ",\"Total\":\"" + result.Rows.Count + "\"}";


            #endregion
        } 
        #endregion
        #region 产品调拔选择产品
        /// <summary>
        ///  产品调拔选择产品 [返回JSONResult]
        /// </summary>
        public string GetProductAllotProducts(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where p.Deleted!=1 and p.BillState=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);

            string clientCode = context.Request.Form["clientCode"];
            string productName = context.Request.Form["productName"];
            string productCode = context.Request.Form["productCode"];
            string productGG = context.Request.Form["productGG"];

            if (!clientCode.IsEmpty())
            {
                whereStr += string.Format(@"  and p.ClientID= '{0}'", clientCode);
            }
            if (!productName.IsEmpty())
            {
                whereStr += string.Format(@" and (a.ProductName like '%{0}%' or a.ProductPyName like '%{0}%')", productName);
            }
            if (!productCode.IsEmpty())
            {
                whereStr += string.Format(@" and (a.ProductCode like '%{0}%')", productCode);
            }
            if (!productGG.IsEmpty())
            {
                whereStr += string.Format(@" and (a.ProductStandard like '%{0}%')", productGG);
            }
            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? " p.SellTime desc " : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)    from T_PD_ProductSellBillDetail a
inner join T_PD_ProductSellBill p on a.ProductSellBillID=p.ProductSellBillID
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join T_Base_ProductInfo  c on c.ProductID=b.ProductID
left join dbo.T_PD_Storehouse d on b.StorehouseID=d.StorehouseID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,b.StockID,a.ProductSellBillDetailID,c.ProductID,c.ProductName,c.ProductStandard,c.ProductCode,d.StorehouseName,p.ProductSellBillo,p.SellTime,a.SellAmount,a.SellPrice,f.TypeName from T_PD_ProductSellBillDetail a
inner join T_PD_ProductSellBill p on a.ProductSellBillID=p.ProductSellBillID
inner join dbo.T_PD_Stock b on a.StockID=b.StockID
inner join T_Base_ProductInfo  c on c.ProductID=b.ProductID
inner join dbo.T_Base_ProductType f on f.TypeID=c.ProductTypeID
left join dbo.T_PD_Storehouse d on b.StorehouseID=d.StorehouseID {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 日常开支
        /// <summary>
        ///  日常开支 [返回JSONResult]
        /// </summary>
        public string GetUsualFinances(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 and b.Deleted!=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            string ProjName = context.Request.Form["ProjName"];
            string selType = context.Request.Form["selType"];
            string selState = context.Request.Form["selState"];


            if (!ProjName.IsEmpty())
            {
                whereStr += string.Format(@" and r.RefeName like '%{0}%'", ProjName);
            }
            if (!selType.IsEmpty() && selType!="-1")
            {
                whereStr += string.Format(@" and a.ProjectType='{0}'", selType);
            }
            if (!selState.IsEmpty() && selState != "-1")
            {
                whereStr += string.Format(@" and b.BillState='{0}'", selType);
            }
            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and b.CreateTime>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and b.CreateTime<='{0}'", Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }

            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            whereStr += string.Format(@" and b.OrgID in (
	select OrgID from dbo.T_Sys_Org org where org.Code='{0}'
) ", orgCode);
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "b.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)    from T_FI_UsualFinanceDetail a inner join T_FI_UsualFinance b
on a.UsualFinance_ID=b.UsualFinance_ID
inner join dbo.T_Sys_Refe r on r.RefeID=a.RefeID
inner join dbo.T_Sys_Org o on o.OrgID=b.OrgID
left join dbo.T_Sys_User u on u.UserID=a.Operator
inner join dbo.T_Sys_User uc on uc.UserID=b.CreateID  {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, b.UsualFinance_ID Id,a.UsualFinanceDetailID detailId, o.OrgName,r.RefeName,a.ProjectType,u.[Name],a.HandlingTime,a.GenerateFee,a.Remark,
b.BillState,uc.[Name] UCName,b.CreateTime
,case when b.BillState=0 then '保存' else '提交' end BillStateName,
case a.ProjectType when 1 then '支出' else '收入' end ProjectTypeName
from T_FI_UsualFinanceDetail a inner join T_FI_UsualFinance b
on a.UsualFinance_ID=b.UsualFinance_ID
inner join dbo.T_Sys_Refe r on r.RefeID=a.RefeID
inner join dbo.T_Sys_Org o on o.OrgID=b.OrgID
left join dbo.T_Sys_User u on u.UserID=a.Operator
inner join dbo.T_Sys_User uc on uc.UserID=b.CreateID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 获取行政地区
        /// <summary>
        ///  行政地区 [返回JSONResult]
        /// </summary>
        public string GetAddressAreas(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            //string orgCode = context.Request.Form["orgCode"];

            string parentId = context.Request.Form["ParentNodeId"];
           // string LinkName = context.Request.Form["LinkName"];


         
                whereStr += string.Format(@" and a.ParentId ='{0}' ", parentId);
            
           

            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.AreaCode" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)  from T_Base_AddressArea a  {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum, a.AddressAreaID ,a.ParentId,a.AreaName,a.AreaCode from T_Base_AddressArea a
{3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        public IList<SelectListItem> GetAddressAreas(int areaId,bool isProvoce)
        {

            string whereStr = " ";
            if (!isProvoce)//非省级
            {
                whereStr = string.Format(" where a.ParentId = {0} ", areaId);
            }
            else//省级
            {
                whereStr = string.Format(" where a.ParentId = 1 ");
            }
            var table = SqlHelperEnhance.ExecuteReaderText(string.Format(@"select   a.AddressAreaID ,a.ParentId,a.AreaName,a.AreaCode from T_Base_AddressArea a {0}", whereStr));
            if (table != null && table.Rows.Count > 0)
            { 
                var len=table.Rows.Count ;
                var result = new List<SelectListItem>();
                for (int i = 0; i < len; i++)
                {
                    result.Add(new SelectListItem { 
                         Value=table.Rows[i]["AddressAreaID"].ToString(),
                         Text = table.Rows[i]["AreaName"].ToString(),
                         Selected = areaId.ToString() == table.Rows[i]["AddressAreaID"].ToString()?true:false
                    });
                }
                return result;
            }

            return null;
        }


        #endregion
        #region 计件工资
        /// <summary>
        ///  计件工资 [返回JSONResult]
        /// </summary>
        public string GetWagesBillDetails(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where  p.IsSettleWage!=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse((context.Request["page"]??"0").ToString(), out startIndex);
            Int32.TryParse((context.Request["pageSize"]??"10").ToString(), out pageSize);

           
            string workGroup = context.Request["workGroup"];
            string isNoInit = context.Request["isNoInit"];
            bool isInit = true;
            if (!isNoInit.IsEmpty() && isNoInit == "1")
            {
                isInit = false;
            }

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"]; 

            string ScrapMoney = context.Request.Form["ScrapMoney"];//扣废品费 :废品费每个组每人扣费一样
            string HeadmanAward = context.Request.Form["HeadmanAward"];//组长提成 :组长提管理费由成员平摊
            string SubsidyMoney = context.Request.Form["SubsidyMoney"];//补贴药费 :所有人一样

            if (!workGroup.IsEmpty() && workGroup != null)
            {
               
                    whereStr += string.Format(" and r.RefeID='{0}' ", workGroup);
                
            }
        

            whereStr += string.Format(" and (p.SubmitDate>='{0}' and p.SubmitDate<='{1}') ", StartDate,Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            whereStr += @" and p.IsSettle=0 ";
            #endregion
            startIndex = startIndex > 0 ? startIndex - 1 : 0;

            #region Ado.Net查询
            string orderByStr = orderBy == "" ? " u.Name desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)      
from 
 (
select wg.IsHeadman,r.RefeID, r.RefeName,u.Name,max(p.SubmitDate)SubmitDate,sum(isnull(p.PieceCount*pp.HourMoney,0)) PieceWages
,case when wg.IsHeadman=1 then '组长' else '组员' end IsHeadmanName,
wg.WorkGroupID
  from dbo.T_Sys_Refe r
 inner join T_FI_WorkGroup wg on wg.GroupName=r.RefeCode
 inner join T_Sys_User u on u.UserID=wg.UserID
 inner join T_FI_Piece p on p.WorkGroupID=wg.WorkGroupID
 inner join T_FI_ProductProcedure pp on pp.ProductProcedureID=p.ProductProcedureID
{0}
 group by wg.IsHeadman, r.RefeName,u.Name,wg.IsHeadman,wg.WorkGroupID,r.RefeID
 )u ", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,u.*,
0.00 ScrapMoney,0.00 HeadmanAward,0.00 LiftMoney,0.00 OtherMoney,0.00 SubsidyMoney,0.00 Wages,0 BillState,
 ''  BillStateName ,
''  IsSettleName,
'' WagesBillID,'' Id ,'' WagesBillNO
  from 
 (
select wg.IsHeadman,r.RefeID, r.RefeName,u.Name,max(p.SubmitDate)SubmitDate,sum(isnull(p.PieceCount*pp.HourMoney,0)) PieceWages
,case when wg.IsHeadman=1 then '组长' else '组员' end IsHeadmanName,
wg.WorkGroupID
  from dbo.T_Sys_Refe r
 inner join T_FI_WorkGroup wg on wg.GroupName=r.RefeCode
 inner join T_Sys_User u on u.UserID=wg.UserID
 inner join T_FI_Piece p on p.WorkGroupID=wg.WorkGroupID
 inner join T_FI_ProductProcedure pp on pp.ProductProcedureID=p.ProductProcedureID
{3}
 group by wg.IsHeadman, r.RefeName,u.Name,wg.IsHeadman,wg.WorkGroupID,r.RefeID
 )u   ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));


             

                #region 计算费用
                if (isInit)
                {
                    if (result != null && result.Rows.Count > 0)
                    {
                        #region 转化指定的费用
                        decimal fp, zztc, btyf;
                        decimal.TryParse(ScrapMoney, out fp);//扣废品费
                        decimal.TryParse(HeadmanAward, out zztc);//组长提成
                        decimal.TryParse(SubsidyMoney, out btyf);//补贴药费
                        #endregion
                        var rows = result.Rows.Count;
                        for (int i = 0; i < rows; i++)
                        {
                            result.Rows[i]["WagesBillID"] = SystemConfig.emptyGuid.ToString();

                            result.Rows[i]["ScrapMoney"] =  string.Format("-{0:f}",(fp / rows));
                            var HeadmanAwardTemp = (rows - 1) > 0 ? (zztc / (rows - 1)) : 0;
                            result.Rows[i]["SubsidyMoney"] =  string.Format("{0:f}",(btyf / rows));
                            
                            if (result.Rows[i]["IsHeadman"].ToString() == "1")
                            {
                                if (rows == 1)
                                {
                                    zztc = 0m;
                                }
                                
                                    result.Rows[i]["HeadmanAward"] = zztc.ToString();
                                    result.Rows[i]["Wages"] = string.Format("{0:f}", (Convert.ToDecimal(result.Rows[i]["PieceWages"]) + Convert.ToDecimal(result.Rows[i]["ScrapMoney"]) + Convert.ToDecimal(result.Rows[i]["SubsidyMoney"]) + zztc));
           
                            }
                            else
                            {
                                result.Rows[i]["HeadmanAward"] =string.Format("-{0:f}", HeadmanAwardTemp);
                                result.Rows[i]["Wages"] = string.Format("{0:f}", (Convert.ToDecimal(result.Rows[i]["PieceWages"]) + Convert.ToDecimal(result.Rows[i]["ScrapMoney"]) - HeadmanAwardTemp + Convert.ToDecimal(result.Rows[i]["SubsidyMoney"])));
                            }

                        }
                    }
                }
                #endregion
                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        ///  计件工资 [返回JSONResult]
        /// </summary>
        public string GetWagesBillDetailsById(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where rt.ModuleName='工作组名称' ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);

            string id = context.Request.Form["id"];

             whereStr += string.Format(" and wb.WagesBillID='{0}' ", id);

            startIndex = startIndex > 0 ? startIndex - 1 : 0;



            #endregion


            #region Ado.Net查询
            string orderByStr = orderBy == "" ? " u.Name desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)    from T_FI_WagesBillDetail w 
inner join T_FI_WagesBill wb  on wb.WagesBillID=w.WagesBillID
 inner join T_FI_WorkGroup p on p.WorkGroupID=w.WorkGroupID
inner join T_Sys_User u on u.UserID=p.UserID
inner join dbo.T_Sys_Refe r on r.RefeCode=p.GroupName
inner join dbo.T_Sys_RefeType rt on rt.TypeCode=r.TypeCode
{0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,u.Name,r.RefeName,w.PieceWages,w.ScrapMoney,w.HeadmanAward,w.LiftMoney,w.OtherMoney,w.SubsidyMoney,w.Wages,isnull(wb.BillState,0) BillState,
case when wb.BillState=0 then '保存' when wb.BillState=1 then '提交' else '' end BillStateName ,
case when wb.IsSettle=0 then '保存' when wb.BillState=1 then '已结算' else '' end IsSettleName,
case when p.IsHeadman=1 then '组长' else '组员' end IsHeadmanName,p.WorkGroupID,
w.WagesBillID WagesBillID,w.WagesBillDetailID Id  from T_FI_WagesBillDetail w 
inner join T_FI_WagesBill wb  on wb.WagesBillID=w.WagesBillID
 inner join T_FI_WorkGroup p on p.WorkGroupID=w.WorkGroupID
inner join T_Sys_User u on u.UserID=p.UserID
inner join dbo.T_Sys_Refe r on r.RefeCode=p.GroupName
inner join dbo.T_Sys_RefeType rt on rt.TypeCode=r.TypeCode
{3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                
                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        /// <summary>
        ///  计件工资主数据（主列表） [返回JSONResult]
        /// </summary>
        public string GetWagesBills(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);

            string billNo = (context.Request.Form["billNo"]??"").Trim();
            string groupName = (context.Request.Form["groupName"]??"").Trim();
            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            if (!billNo.IsEmpty())
            {
                whereStr += string.Format(@" and (a.wagesbillno like '%{0}%') ", billNo);
            }
            if (!string.IsNullOrEmpty(groupName))
            {
                whereStr += string.Format(@" and (exists
 (
	select top 1 1 from dbo.T_FI_WagesBillDetail b 
 inner join T_FI_WorkGroup w on b.WorkGroupID=w.WorkGroupID
 inner join dbo.T_Sys_Refe r on r.RefeCode=w.GroupName
 where b.WagesBillID=a.WagesBillID and like '%{0}%'
 ) ) ", groupName);
            }
            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.startDate>= '{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.EndDate<= '{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }
          
            startIndex = startIndex > 0 ? startIndex - 1 : 0;



          //  whereStr += " and wg.Deleted=0 ";
            #endregion

            #region Ado.Net查询
            string orderByStr = orderBy == "" ? " a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)    from T_FI_WagesBill a {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.*,
(
 select top 1 r.RefeName GroupName from dbo.T_FI_WagesBillDetail b 
 inner join T_FI_WorkGroup w on b.WorkGroupID=w.WorkGroupID
 inner join dbo.T_Sys_Refe r on r.RefeCode=w.GroupName
 where b.WagesBillID=a.WagesBillID
 ) WorkGroupName,
case when a.BillState=0 then '保存' when a.BillState=1 then '提交' else '' end BillStateName
,bill.* 
 from dbo.T_FI_WagesBill a
 inner join 
 (
  select b.WagesBillID WagesBillID1,SUM(b.PieceWages) PieceWages1,SUM(b.LiftMoney)LiftMoney1,SUM(b.OtherMoney)OtherMoney1,SUM(b.wages)wages1 from  T_FI_WagesBillDetail b
 group by b.WagesBillID
 )bill
 on a.WagesBillID=bill.WagesBillID1  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                
                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 计件管理
        /// <summary>
        ///  计件管理 [返回JSONResult]
        /// </summary>
        public string GetPieceLists(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where rt.moduleName='工作组名称' ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);

            string userName = context.Request.Form["userName"];
            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];

            if (!userName.IsEmpty())
            {
                whereStr += string.Format(@" and (c.Name like '%{0}%') ", userName);
            }
            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.SubmitDate>= '{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.SubmitDate<= '{0}'",Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }

            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            #endregion
            #region Ado.Net查询
            string orderByStr = orderBy == "" ? "a.SubmitDate " : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1) from T_FI_Piece a
 inner join T_FI_WorkGroup b on  a.WorkGroupID=b.WorkGroupID
 inner join dbo.T_Sys_User c on b.UserID=c.UserID
 inner join T_FI_ProductProcedure d on d.ProductProcedureID=a.ProductProcedureID
 inner join  dbo.T_Sys_Refe r on r.RefeCode=b.GroupName
 inner join dbo.T_Sys_RefeType rt on rt.TypeCode=r.TypeCode {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.pieceID Id,a.*,c.Name WorkGroupName,d.ProcedureName ProductProcedureName,
 case when b.IsHeadman=1 then '组长' else '组员' end IsHeadmanName,r.RefeName , case when a.IsSettleWage=0 then '未结算' else '结算' end SettleState
,case when a.BillState=0 then '保存' when a.BillState=1 then '提交' else '' end BillStateName
  from T_FI_Piece a
 inner join T_FI_WorkGroup b on  a.WorkGroupID=b.WorkGroupID
 inner join dbo.T_Sys_User c on b.UserID=c.UserID
 inner join T_FI_ProductProcedure d on d.ProductProcedureID=a.ProductProcedureID
 inner join  dbo.T_Sys_Refe r on r.RefeCode=b.GroupName
 inner join dbo.T_Sys_RefeType rt on rt.TypeCode=r.TypeCode {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 汇款记录
        /// <summary>
        ///  汇款记录 [返回JSONResult]
        /// </summary>
        public string GetRemittanceRecords(HttpContextBase context)
        {
            int startIndex = 0;
            int pageSize = 10;
            string orderBy = "";
            string whereStr = " where 1=1 and a.Deleted!=1 ";
            int recordCount = 0;
            #region 变量赋值
            Int32.TryParse(context.Request.Form["page"].ToString(), out startIndex);
            Int32.TryParse(context.Request.Form["pageSize"].ToString(), out pageSize);
            string orgCode = context.Request.Form["orgCode"];

            string StartDate = context.Request.Form["StartDate"];
            string EndDate = context.Request.Form["EndDate"];
            string RemittancePeoper = context.Request.Form["RemittancePeoper"];

            string IsCommit = context.Request.Form["IsCommit"];

            if (!IsCommit.IsEmpty() && IsCommit=="1")
            {
                whereStr += string.Format(@" and a.BillState=1 ");
            }

            if (!RemittancePeoper.IsEmpty())
            {
                whereStr += string.Format(@" and a.RemittancePeoperName like '%{0}%'", RemittancePeoper);
            }

            startIndex = startIndex > 0 ? startIndex - 1 : 0;
            if (!orgCode.IsEmpty())
            {
                whereStr += string.Format(@" and a.OrgID in (
	select OrgID from dbo.T_Sys_Org org where org.Code='{0}'
) ", orgCode);
            }

            if (StartDate.IsData())
            {
                whereStr += string.Format(@" and a.RemittanceTime>='{0}'", StartDate);
            }
            if (EndDate.IsData())
            {
                whereStr += string.Format(@" and a.RemittanceTime<='{0}'", Convert.ToDateTime(EndDate).AddDays(1).ToString("yyyy-MM-dd"));
            }
            #endregion
            #region Ado.Net查询


            string orderByStr = orderBy == "" ? "a.CreateTime desc" : orderBy;

            recordCount = SqlHelperEnhance.ExecuteScalQueryText(string.Format(@"select count(1)   from T_FI_RemittanceRecord a
inner join T_Sys_Org b on a.OrgID=b.OrgID
inner join T_Sys_User c on a.RemittancePeoper=c.UserID {0}", whereStr));
            if (recordCount > 0)
            {
                var result = SqlHelperEnhance.ExecuteReaderText(string.Format(@"with paging as(select ROW_NUMBER() over (order by  {2} ) rownum,a.RemittanceMoney, a.RemittanceRecordID Id,a.OrgID,a.RemittanceTime,a.RemittanceBank,a.RemittanceNumber,a.Remarks
,b.OrgName,c.[Name] RemittancePeoperName,a.BillState
,case when a.BillState=0 then '保存' else '提交' end BillStateName,a.CreateTime
,case when a.AffimState=0 then '' else '确认' end AffimStateName,a.AffimDate,a.AffimState
,(select top 1 [Name] from T_Sys_User where UserID=a.AffimorID) AffimorName
from T_FI_RemittanceRecord a
inner join T_Sys_Org b on a.OrgID=b.OrgID
inner join T_Sys_User c on a.RemittancePeoper=c.UserID  {3} ) select * from paging p where p.rownum>{0} and p.rownum<={1}", startIndex * pageSize, (startIndex * pageSize) + pageSize, orderByStr, whereStr));

                var target = Newtonsoft.Json.JsonConvert.SerializeObject(result);
                return target = "{\"Rows\":" + target + ",\"Total\":\"" + recordCount + "\"}";
            }
            else
            {
                return "";
            }

            #endregion
        }
        #endregion
        #region 验证数据(最不应该这样做的，但是时间紧，没办法)
        
        public bool ValidateOutOfLastSellOutNum(Guid stockId, float validateNum,bool isStockId=true)
        {

            var whereStr ="";
            if (isStockId)
            {
                whereStr = string.Format(@" b.StockID='{0}'", stockId.ToString());
            }
            else
            {
                whereStr = string.Format(@" b.ProductSellBillDetailID='{0}'", stockId.ToString());
            }
           var recordCount = SqlHelperEnhance.ExecuteScalQueryText<float>(string.Format(@"select top 1 (b.SellAmount+b.ReadyCount) sellCount from dbo.T_PD_ProductSellBillDetail b
inner join T_PD_ProductSellBill a on a.ProductSellBillID=b.ProductSellBillID
where {0}
order by a.SellTime desc ", whereStr));

           if (recordCount < validateNum)
           {
               return true;
           }
           return false;
           
        }
        //验证产品新增或修改时有无重复（判断条件是：名称+规格 OR 名称+编码）
        public bool ValidateProductIsExist(ProductInfoDto product)
        {

             var recordCount = SqlHelperEnhance.ExecuteScalQueryText<int>(string.Format(@"select count(0) from dbo.T_Base_ProductInfo where (ProductName='{0}' and ProductStandard='{1}')or(ProductName='{0}' and ProductCode='{2}') ", product.ProductName.Trim(),product.ProductStandard.Trim(),product.ProductCode.Trim()));

            if (recordCount >0)
            {
                return true;
            }
            return false;
        }
        #endregion
    }
}